Link to home
Start Free TrialLog in
Avatar of wasimmm
wasimmm

asked on

mysql joining from the same table

I have table with the following fields; in a table called wp_routers

manufacturer,model,target_url,credits

Asus,WRT-54G,null,1
Asus,WRT-54G,null,1
Asus,WRT-54G,/asus,1
Cisco, C7600,null,1
Cisco, C7600,null,1
Cisco, C7600,null,1
Netgear, H8766,null,1
Netgear, H8766,null,1
Netgear, H8766,null,1


I need a query to retreive all the manufacturer and target url once only. The target url can either be null or the first target url found. For example for the above data i would like returned

Asus,/asus
Cisco, null
Netgear,null

I have tried

SELECT distinct manufacturer,target_url FROM wp_routers WHERE credits='1' order by manufacturer

But that returns

Asus,null
Asus,/asus
Cisco.null
Netgear,null

I dont want the manufacturer to be duplicated. I want it to either return null or the target url if it has one.
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Hi,
Please try below-
SET @row_number = 0;
SELECT manufacturer,model,target_url,credits FROM
(
    SELECT *,(@row_number:= CASE WHEN @customer_no = manufacturer THEN @row_number + 1 ELSE 1 END) AS num , @customer_no:= manufacturer as CustomerNumber
    FROM 
    (
        SELECT *
        FROM
        (
           SELECT 'Asus' manufacturer ,'WRT-54G' model ,null target_url ,1 credits UNION ALL
           SELECT 'Asus','WRT-54G',null,1 UNION ALL
           SELECT 'Asus','WRT-54G','/asus',1 UNION ALL
           SELECT 'Cisco', 'C7600',null,1 UNION ALL
           SELECT 'Cisco', 'C7600',null,1 UNION ALL
           SELECT 'Cisco', 'C7600',null,1 UNION ALL
           SELECT 'Netgear', 'H8766',null,1 UNION ALL
           SELECT 'Netgear', 'H8766',null,1 UNION ALL
           SELECT 'Netgear', 'H8766',null,1
        )k 
    )p ,(SELECT @customer_no:=0,@row_number:=0) as t
    ORDER BY manufacturer,target_url DESC
)z WHERE num = 1

Open in new window


Output

/*------------------------

OUTPUT

------------------------*/
manufacturer model   target_url credits
------------ ------- ---------- -----------
Asus         WRT-54G /asus      1
Cisco        C7600   NULL       1
Netgear      H8766   NULL       1

(3 row(s) affected)

Open in new window


For your query you need use...below-

--

SET @row_number = 0;
SELECT manufacturer,model,target_url,credits FROM
(
    SELECT *,(@row_number:= CASE WHEN @customer_no = manufacturer THEN @row_number + 1 ELSE 1 END) AS num , @customer_no:= manufacturer as CustomerNumber
    FROM 
    (     
		SELECT manufacturer,model,target_url,credits FROM wp_routers       
    )p ,(SELECT @customer_no:=0,@row_number:=0) as t
    ORDER BY manufacturer,target_url DESC
)z WHERE num = 1

--

Open in new window


Hope it helps!
If you looking for MS SQL Server then use below-

/*------------------------
SELECT manufacturer,model,target_url,credits FROM
(
    SELECT *,ROW_NUMBER() OVER (PARTITION BY manufacturer ORDER BY target_url DESC) rnk
    FROM 
    (
        SELECT *
        FROM
        (
           SELECT 'Asus' manufacturer ,'WRT-54G' model ,null target_url ,1 credits UNION ALL
           SELECT 'Asus','WRT-54G',null,1 UNION ALL
           SELECT 'Asus','WRT-54G','/asus',1 UNION ALL
           SELECT 'Cisco', 'C7600',null,1 UNION ALL
           SELECT 'Cisco', 'C7600',null,1 UNION ALL
           SELECT 'Cisco', 'C7600',null,1 UNION ALL
           SELECT 'Netgear', 'H8766',null,1 UNION ALL
           SELECT 'Netgear', 'H8766',null,1 UNION ALL
           SELECT 'Netgear', 'H8766',null,1
        )k 
    )p    
)z WHERE rnk = 1

------------------------*/
manufacturer model   target_url credits
------------ ------- ---------- -----------
Asus         WRT-54G /asus      1
Cisco        C7600   NULL       1
Netgear      H8766   NULL       1

(3 row(s) affected)

Open in new window


Hope it helps!
Avatar of Ryan Chong
IF your records come with a unique identifier field, such as ID or DateTime, you probably can try something like this as well.

for example, using ID field for comparison (without using a variable)
SELECT a.* FROM wp_routers a
inner join
(
	select min(b.id) id from wp_routers b
    group by b.manufacturer
) b on a.id = b.id
where a.target_url is not null

union

SELECT a.* FROM wp_routers a
inner join
(
	select min(b.id) id from wp_routers b
    group by b.manufacturer
) b on a.id = b.id
left join 
(
	SELECT a.* FROM wp_routers a
	inner join
	(
		select min(b.id) id from wp_routers b
		group by b.manufacturer
	) b on a.id = b.id
	where a.target_url is not null
) c
on a.manufacturer = c.manufacturer
where a.target_url is null and c.manufacturer is null

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Julian Hansen
Julian Hansen
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wasimmm
wasimmm

ASKER

Thank you Julian. Glad to find this MAX() solution.
You are welcome.