/    Sign up×
Community /Pin to ProfileBookmark

Problem: I have to tables in one DB im trying to compare the ProjectProperties with the UserProperties, if i run this query it will only return my ProjectProperties its probably something very easy im overlooking
.

Thanks in advance!

SELECT projects.ProjectProperty1, projects.ProjectProperty2, users.UserProperty1, users.UserProperty2
FROM projects, users
WHERE projects.ProjectProperty1 LIKE CONCAT(‘%’, users.UserProperty1, ‘%’) OR projects.ProjectProperty2 LIKE CONCAT(‘%’, users.UserProperty2, ‘%’) OR users.UserProperty1 LIKE CONCAT(‘%’, projects.ProjectProperty1, ‘%’) OR users.UserProperty2 LIKE CONCAT(‘%’, projects.ProjectProperty2, ‘%’),

to post a comment
PHP

5 Comments(s)

Copy linkTweet thisAlerts:
@ginerjmSep 10.2019 — A quick re-write of your query to make it easier to read and interpret makes it look like this:
[CODE]
$qry = "SELECT p.ProjectProperty1, p.ProjectProperty2, u.UserProperty1, u.UserProperty2
FROM projects p, users u
WHERE p.ProjectProperty1 LIKE CONCAT('%', users.UserProperty1, '%') OR
p.ProjectProperty2 LIKE CONCAT('%', users.UserProperty2, '%') ,";
[/code]


Where is the rest of this query? Note that I dropped your redundant 'likes' since one is as good as the other.

It does appear that you database design is flawed in that you have multiple data items of the same 'thing' stored on each record. A proper design would break those properties out to a separate table that has only one of them per record.

As for your results - without seeing the rest of the query it is hard to continue.
Copy linkTweet thisAlerts:
@NogDogSep 10.2019 — > @ginerjm#1608569 I dropped your redundant 'likes' since one is as good as the other.

Well...not necessarily. The use of LIKE with wildcard '%' characters may mean in would match in one direction but not the other. However...

> @ginerjm#1608569 It does appear that you database design is flawed

...the fact that all those LIKEs are required instead of a simple JOIN or two is definitely of concern in terms of data normalization, etc. They also mean that the query will have to do a full table read every time, which will not scale well as the amount of data increases (it can't leverage any column indexes).

As to...

> @MaxiFakeTaxi#1608558 this query it will only return my ProjectProperties

...If a database redesign is not an option, using foreign keys and such for more normalized table joins; then without some sample data and examples of what you expect to see versus what you are actually seeing, it's difficult to answer.
Copy linkTweet thisAlerts:
@ginerjmSep 10.2019 — I stand correced :(
Copy linkTweet thisAlerts:
@HarshShahOct 31.2019 — Hi

you can use joins in your query.


Select p.ProjectProperty1, p.ProjectProperty2, u.UserProperty1, u.UserProperty2 from projects AS p RIGHT JOIN users AS u ON p.projectId = u.usersId

This query is show that you could use joins. here you can apply where condition according to your need to compare ProjectProperty with UserProperty.

Let me know if you have any doubt in this or need more help.

Hope This will help you.

Thank you.
Copy linkTweet thisAlerts:
@bblfoodsNov 02.2019 — SQL, Structured Query Language, is a programming language designed to manage data stored in relational databases. SQL operates through simple, declarative statements. This keeps data accurate and secure, and it helps maintain the integrity of databases, regardless of size.
×

Success!

Help @MaxiFakeTaxi spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 BETA 3.28,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,

tipper: Anonymous,
tipped: article
amount: 10 SATS,
)...