只筛选出一些与SQL有共同点的参数

I have now built a SQL command that can list a result with several inputs.

And it look like this with input ID 30 & 31

    $sql ="SELECT ItemRelation.ItemRelTo, ItemRelation.Item, Items.CatID, Items.ItemID,   
    Items.Title, Items.Image, Items.Desc, Items.TimeStamp
    FROM Items
    INNER JOIN ItemRelation 
    ON ItemRelation.ItemRelTo=Items.ItemID
    WHERE ItemRelation.Item  In (30,31) 
    UNION
    SELECT ItemRelation.ItemRelTo, ItemRelation.Item, Items.CatID, Items.ItemID,     
    Items.Title, Items.Image, Items.Desc, Items.TimeStamp
    FROM Items
    INNER JOIN ItemRelation
    ON ItemRelation.Item=Items.ItemID
    WHERE ItemRelation.ItemRelTo In (30,31)";

And my Relationship tabel is

Item    ItemRelTo
30      10
31      12
11      12
11      31
30      11

So the result from my SQL is Items.ItemID 10,11,11,12 so I get duplicates of item 11.

And that i really want is what my input (30,31) has in common, and that is Items.ItemID = 11 and not a duplicate value, so just one result.

And I'm rather new to this so please explain so an idiot can understand =)

The rest of the code how i display my results is

$result = mysqli_query($con,$sql);
while($row = mysqli_fetch_array($result))
{
    echo '<tr>';
    echo '<td>' . $row['ItemID'] . '</td>';
    echo '<td>' . $row['Item'] . '</td>';
    echo '<td>' . $row['CatID'] . '</td>';
    echo '<td>' . $row['Title'] . '</td>';
    echo '<td><img src="Image/',$row['Image'],'"></td>';
    echo '<td>' . $row['Desc'] . '</td>';
    echo '<td>' . $row['TimeStamp'] . '</td>';
    echo '</tr>'; 
}

This became my solution. Probably not he best way to use a Count when the database grows. And thanks to Mike for helping me get there!

SELECT   Items.ItemID, GROUP_CONCAT(ItemRelation.ItemRelTo) AS ItemRelTo, Items.CatID,    
Items.Title, Items.Image, Items.Desc, Items.TimeStamp
FROM Items
INNER JOIN ItemRelation 
ON Items.ItemID = ItemRelation.Item
Where ItemRelTo IN (30,31)
Group By ItemID
HAVING COUNT(Items.ItemID) > 1

This became my solution. Probably not he best way to use a Count when the database grows. And thanks to Mike for helping me get there!

SELECT   Items.ItemID, GROUP_CONCAT(ItemRelation.ItemRelTo) AS ItemRelTo,
Items.CatID, Items.Title, Items.Image, Items.Desc, Items.TimeStamp
FROM Items
INNER JOIN ItemRelation 
ON Items.ItemID = ItemRelation.Item
Where ItemRelTo IN (30,31)
Group By ItemID
HAVING COUNT(Items.ItemID) > 1

First of all, I wouldn't use a UNION. Yo can just as easily write this SQL as follows:

SELECT
  ItemRelation.ItemRelTo,
  ItemRelation.Item,
  Items.CatID,
  Items.ItemID,   
  Items.Title,
  Items.Image,
  Items.Desc,
  Items.TimeStamp
FROM Items
FULL JOIN ItemRelation
WHERE
  (
    Items.ItemID = ItemRelation.Item
    OR Items.ItemID = ItemRelation.ItemRelTo
  ) AND
  (
    ItemRelation.Item IN(30,31)
    OR ItemRelation.ItemRelTo IN(30,31)
  )

Second, in order to just show DISTINCT id's you will need to either, remove the ItemRelation data from the select and use SELECT DISTINCT Items.ItemID which would look like this:

SELECT
  DISTINCT Items.ItemID,
  Items.CatID, 
  Items.Title,
  Items.Image,
  Items.Desc,
  Items.TimeStamp
FROM Items
FULL JOIN ItemRelation
WHERE
  (
    Items.ItemID = ItemRelation.Item
    OR Items.ItemID = ItemRelation.ItemRelTo
  ) AND
  (
    ItemRelation.Item IN(30,31)
    OR ItemRelation.ItemRelTo IN(30,31)
  )

OR you could use GROUP BY Items.ItemID, but then you would need to decide how the differing values for the ItemRelation data will be presented. As doing GROUP BY without a specific aggregation is going to give you unpredictable results. For demonstration purposes, I might suggest using GROUP_CONCAT

SELECT
  Items.ItemID,
  GROUP_CONCAT(ItemRelation.ItemRelTo) AS ItemsRelatedTo,
  GROUP_CONCAT(ItemRelation.Item) AS ItemRelations,
  Items.CatID, 
  Items.Title,
  Items.Image,
  Items.Desc,
  Items.TimeStamp
FROM Items
FULL JOIN ItemRelation
WHERE
  (
    Items.ItemID = ItemRelation.Item
    OR Items.ItemID = ItemRelation.ItemRelTo
  ) AND
  (
    ItemRelation.Item IN(30,31)
    OR ItemRelation.ItemRelTo IN(30,31)
  )
GROUP BY Items.ItemID

The REAL issue here is your schema. If these relations are many-to-many and non-directional (which it appears to be by the way you are trying to query this data), then what you should be doing is putting both the forward AND backwards relations into you relations table when establishing the relationships. This allows lookup in either direction and prevents you from needing to do what amounts to duplicate WHERE clauses in your query.

So updating your example data you would have this:

Item    ItemRelTo
30      10
10      30
31      12
12      31
11      12
12      11
11      31
31      11
30      11
11      30

This would allow you to use a greatly simplified query to find any items that have a relation

SELECT
  Items.ItemID,
  GROUP_CONCAT(ItemRelation.ItemRelTo) AS ItemsRelatedTo,
  Items.CatID, 
  Items.Title,
  Items.Image,
  Items.Desc,
  Items.TimeStamp
FROM Items
INNER JOIN ItemRelation
  ON Items.ItemID = ItemRelation.Item
WHERE ItemRelation.ItemRelTo IN(30,31)
GROUP BY Items.ItemID