Combining two queries

[runrunforest]

$query1 = "SELECT category1
				, category2
				, category3
 
			FROM listing
			WHERE username = '{$username}'
			AND id = {$listing_id}  ";

$query2 = "SELECT meaning 
			FROM category_dict
			WHERE abbreviation in ('".$rows['category1']."'
									,'".$rows['category2']."'
									,'".$rows['category3']."')";

I have two queries as above

The table structure is as here:

listing(title, category1, category2, category3, price, closing)

category_dict(abbreviation, meaning)

I need help to combine them correctly.

[Mittineague]

Are you looking for UNION?

$query = "SELECT title
                , category1
                , category2
                , category3
                , price
                , closing
            FROM listing
            WHERE username = '{$username}'
            AND id = {$listing_id}
           UNION
           SELECT meaning 
            FROM category_dict
            WHERE abbreviation in ('".$rows['category1']."'
                                    ,'".$rows['category2']."'
                                    ,'".$rows['category3']."')"; 

[runrunforest]

Thanks Mittineague

But the $rows['category1'] ,$rows['category2'], $rows['category3'] only obtained after the query1, using fetch result.

As we see in your example, the $rows variables weren't yet define, we can not use them in the query.

[guido2004]

Use join

SELECT 
    category1
  , category2
  , category3
  , meaning
FROM listing
INNER JOIN category_dict
ON abbreviation in (category1, category2, category3)
WHERE username = '{$username}'
AND   id = {$listing_id}

[runrunforest]

Thanks Guido,

The query gives insufficient resources for mysql_fetch_array.

I still need help.

Let me detail the need again.

In table listing(id,title, category1, category2, category3), i want to select the row where id = $listing_id. This query should give back only one row.

Now, in table category_dict(abbreviation, meaning), I want to select meaning, where category1= abbreviation, category2 = abbreviation, category3 = abbreviation. This should give back 3 meanings

Please help!

[r937]

SELECT l.id
     , l.title
     , c1.meaning AS meaning1
     , c2.meaning AS meaning2
     , c3.meaning AS meaning3
  FROM listing AS l
LEFT OUTER
  JOIN category_dict AS c1
    ON c1.abbreviation = l.category1
LEFT OUTER
  JOIN category_dict AS c2
    ON c2.abbreviation = l.category2
LEFT OUTER
  JOIN category_dict AS c3
    ON c3.abbreviation = l.category3
 WHERE l.username = '{$username}'           
   AND l.id = {$listing_id}

[runrunforest]

I was about to go back and post your code is syntax error, but after four days of struggling to find out why a beautifully sense making code can not work. Finally, its because the collation differentiation in some field in two tables.

I am back to good night sleep as normal.

@Guido, sorry, your code probably works too, I haven't tested it again but I'm sure it should work now.

Merry Christmas!