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,credit s
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.
manufacturer,model,target_
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.
If you looking for MS SQL Server then use below-
Hope it helps!
/*------------------------
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)
Hope it helps!
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)
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Julian. Glad to find this MAX() solution.
You are welcome.
Please try below-
Open in new window
Output
Open in new window
For your query you need use...below-
Open in new window
Hope it helps!