Need help to check two tables for users name??

Question

User-1797368610 posted

Hi,

i need help to build a query. There is two tables one is Auditor and another one is Users. In both tables i have first_name and last_name columns. All i want to check existing value during the save. if same first_name and last_name exist any of the table then return count >0.

Thanks in advanced!

Answer 1

User2103319870 posted

Rakib1

if same first_name and last_name exist any of the table then return count as 1 means value exist.

You could do an inner join with both tables like below

SELECT Count(1) AS RESULT
FROM SampleTable1
INNER JOIN SampleTable2 ON SampleTable1.first_name = 'FirstName1' AND SampleTable1.last_name = 'LastName1'
AND SampleTable2.first_name = 'FirstName1' AND SampleTable2.last_name = 'LastName1'

• Sample Demo

EDIT: Updated the demo

Answer 2

User-1797368610 posted

Hi,

Thanks for your reply. As i mentioned  i have two tables and query should check both tables for the existing records. if first name and last name match any of the tables then return count >1 that means value already exist.

i have checked your above query, its just check matching values from two tables.  but i want to count value, if value is more than>0 then dont save database.  fyi , i am using below code:

query = " select count(first_name | | last_name) count from Auditor, Users where first_name = '" & txt_FirstName.Text & _
"' and last_name = " & txt_LastName.Text

post = SavedAlready(query)
If post > 0 Then
alert = "<script language='javascript'>" & _
"alert ('The User name entered is already in the Project managers list.'); " & _
"</script>"
ClientScript.RegisterStartupScript(GetType(Page), "clientScript", alert)
Exit Sub
End If

Answer 3

User-1797368610 posted

i was using below query but getting count as 0 that means no records but as i can see i have record for "Daniel Post" into "Users " table. it should give me count>0.

SELECT Count(cts.first_name | |mngr.last_name) count
FROM Auditors cts
INNER JOIN Users mngr ON cts.first_name = 'Daniel' AND cts.last_name = 'Post'
AND mngr.first_name = 'Daniel' AND mngr.last_name = 'Post'

Answer 4

User-1797368610 posted

Any help will be greatly appreciated. sorry may i wasn't able to properly explain it first. all i am looking if there is name already  exist in any of the tables, Name doesn't need to have both tables. then just count greater than 0. if its return 1 then my code will generate the alert msg that this user already exist.

Answer 5

User2103319870 posted

all i am looking if there is name already  exist in any of the tables, Name doesn't need to have both tables. then just count greater than

You can try with below code

select COUNT(1) as Result
from (
    select first_name,last_name from SampleTable1
    union 
    select first_name,last_name from SampleTable2
) a
where a.first_name = 'FirstName1' and a.last_name = 'LastName1';

• Sample Demo

Answer 6

User269602965 posted

Of course this example uses hard coded names, but in a function you would use BIND VARIABLES to dynamically pass first and last name.

We get unique names from both tables by UNION, then apply the name filter, then produce a result name_exists or no_match

SELECT
  CASE
    WHEN b.CNT > 0 THEN 'NAME_EXISTS'
    WHEN b.CNT = 0 THEN 'NO_MATCH'
   END AS NAME_CHECK
FROM
	(
	SELECT
		COUNT(*) AS CNT
	FROM
		(
		SELECT
			FIRST_NAME,
			LAST_NAME
		FROM
			AUDITOR
		UNION
		SELECT
			FIRST_NAME,
			LAST_NAME
		FROM
			USERS
		) a
	WHERE
		a.FIRST_NAME = 'Harry'
		AND
		a.LAST_NAME  = 'Smith'
	) b
/  

Answer 7

User269602965 posted

I sent code example for finding name in ANY table

Answer 8

User-1797368610 posted

its working perfectly. Appreciated !!

Answer 9

User-1797368610 posted

Now problem with the Case insensitive. To find the data, it has to be exactly same when i entered otherwise query does not return existing value even though same name already in the database. is there any way i could resolve this cumbersome whether i entered user name with upper or lower case?

Again appreciated for your help!

Answer 10

User2103319870 posted

is there any way i could resolve this cumbersome whether i entered user name with upper or lower case?

Try changing your query like below

SELECT
  CASE
    WHEN b.CNT > 0 THEN 'NAME_EXISTS'
    WHEN b.CNT = 0 THEN 'NO_MATCH'
   END AS NAME_CHECK
FROM
	(
	SELECT
		COUNT(*) AS CNT
	FROM
		(
		SELECT
			FIRST_NAME,
			LAST_NAME
		FROM
			SampleTable1
		UNION
		SELECT
			FIRST_NAME,
			LAST_NAME
		FROM
			SampleTable2
		) a
	WHERE
		UPPER(a.FIRST_NAME) = UPPER('Harry')
		AND
		UPPER(a.LAST_NAME)  = UPPER('Smith')
	) b