Nested Mysql query y AS z

Databases
1

SELECT c.*, p.id as promoID FROM cars c, promo p

If I have two tables in MySQL… cars and promo
in this query - if in promo have 1/4 of the cars in cars - promoID field for cars only in cars table what will be?
I want get null or empty string for cars Not in promo…
is this correct? i have 1000 entries in cars and 50 entries in promo from cars table but getting promoID 100 results rather only 50…

I rechecked:
[“promoID”]=> string(6) “236511” rather appear one in a case get it in var_dump in 3 times…
What is correct query???

2

use a LEFT OUTER JOIN

3

OK
thks
well if I want exclude c.id (car id cars with ids in comma separated) how do this? the below does NOT work…

$EXCLUDED_CARS = "230024, 230033";

SELECT c.* FROM  cars c
WHERE c.id NOT IN ($EXCLUDED_CARS)
LEFT JOIN promo p				 
ON c.ID = p.ID 
GROUP BY MAKE
4

LEFT JOIN is part of the FROM clause

you cannot just stick the WHERE clause in there half way through

5

where put it?

6

put the WHERE clause ~after~ the FROM clause

7

ON c.ID = p.ID
WHERE c.id NOT IN ($EXCLUDED_CARS) OR c.make NOT IN ($EXCLUDED_MAKES)
//this works not tested OR yet
can use OR with NOT IN

?

8

WELL HOW USE two NOT IN
???

9

i Think solved used AND in AND NOT IN - Not OR

10

HOW APPLY
IN
not
NOT IN
seems no work?
can be (comma separated)…

                         FROM 

				 cars c
				   
				 LEFT JOIN promo p 
				 
				 ON c.ID = p.ID 
				 
				 WHERE c.id NOT IN ($EXCLUDED_MAKES) 
				 
				 GROUP BY MAKE

			     ORDER BY
11

please show your actual query, not a part of it

also, please test it using actual values, not via php variables

12

i WANT IF C NOT HAVE ENTRY IN P promoID=“” …OTHERWISE IF IT HAS promoID=C.ID HOW MODIFY QUERY?? NOT SEEM TO WORK BELOW ONLY P CARS APPEAR NOT ALSO IN C WITH promoID=“” THAT IS DESIRED

SELECT 

	               c.*, p.id as promoID

	             FROM 

				 cars c
				   
				 LEFT OUTER JOIN promo p 
				 
				 ON c.ID = p.ID 
				 
				 WHERE c.make = 35
				 
				 GROUP BY MAKE

			     ORDER BY 

				   c.SYS_CREATION_DATE DESC 

				 LIMIT 0, 100
13

use
GROUP BY c.make
rather
GROUP BY make
bug found

14

i don’t understand why you’re using GROUP BY begause you’re not totalling anything

try this –

SELECT c.* , COALESCE(p.id,'') as promoID FROM cars c LEFT OUTER JOIN promo p ON p.ID = c.ID WHERE c.make = 35 ORDER BY c.SYS_CREATION_DATE DESC LIMIT 100

16

COALESCE what this do…?

17

[quote=“lsepolis123, post:16, topic:206870, full:true”]COALESCE what this do…?
[/quote]
i don’t mean to be rude, but is google broken for you?

1 Like
18

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.